package com.egao.common.module.system.transfer;

import cn.hutool.core.util.IdUtil;
import com.egao.common.core.web.JsonResult;
import com.egao.common.module.company.constants.CompanyConstants;
import com.egao.common.module.company.entity.Company;
import com.egao.common.module.company.service.CompanyService;
import com.egao.common.module.system.entity.Dict;
import com.egao.common.module.system.entity.User;
import com.egao.common.module.system.entity.UserRole;
import com.egao.common.module.system.service.DictService;
import com.egao.common.module.system.service.UserRoleService;
import com.egao.common.module.system.service.UserService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 单位数据导入
 * @author: dl
 */
@RestController
@Slf4j
public class CompanyDataController {

    @Value("${old.datasource.jyurl}")
    private String url;

    @Value("${old.datasource.username}")
    private String userName;

    @Value("${old.datasource.password}")
    private String password;

    @Value("${minio.old-data-head}")
    private String contentUrl;

    @Value("${minio.endpoint}")
    private String minioUrl;

    @Autowired
    private CompanyService companyService;

    @Autowired
    private UserService userService;

    @Autowired
    private UserRoleService userRoleService;

    @Autowired
    private DictService dictService;

    @Transactional(rollbackFor = Exception.class)
    public JsonResult transfer() {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        long beginTime = System.currentTimeMillis();
        List<Map<String, Object>> areaList = getAreaList();

        List<Dict> natureList = dictService.cacheList(CompanyConstants.NATURE_LIST_TYPE);
        List<Dict> industryList = dictService.cacheList(CompanyConstants.INDUSTRY_LIST_TYPE);

        List<Dict> oldNatureList = dictService.cacheList("company_nature_old");
        List<Dict> oldIndustryList = dictService.cacheList("company_industry_old");
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, userName, password);
            st = conn.createStatement();
            String sql = "select id,loginname,del_flag,password,companyname,organization,organizationurl,businessurl,nature,industry,scale,areaid,locationlevel,contact,tel,email,homepage,zipcode,address,checkstate,checkstatecontent,synopsis,create_date,update_date from  company order by create_date";
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            log.info("总数：{}", rs.getRow());
            int i = 0;
            int j = 0;
            while (rs.next()) {
                String oldId = rs.getString("id");
                String loginName = rs.getString("loginname");
                String del_flag = rs.getString("del_flag");
                String password = rs.getString("password");
                String name = rs.getString("companyname");
                String organization = rs.getString("organization");
                String organizationUrl = rs.getString("organizationurl");
                String businessurl = rs.getString("businessurl");
                Integer nature = rs.getInt("nature");
                Integer industry = rs.getInt("industry");
                Integer scale = rs.getInt("scale");
                String areaid = rs.getString("areaid");
                Integer locationlevel = rs.getInt("locationlevel");
                String contact = rs.getString("contact");
                String tel = rs.getString("tel");
                String email = rs.getString("email");
                String homepage = rs.getString("homepage");
                String zipcode = rs.getString("zipcode");
                String address = rs.getString("address");
                Integer checkstate = rs.getInt("checkstate");
                String checkstatecontent = rs.getString("checkstatecontent");
                Company company = new Company();

                Date createDate = new Date(rs.getTimestamp("create_date").getTime());
                if (rs.getTimestamp("update_date") != null) {
                    Date updateDate = new Date(rs.getTimestamp("update_date").getTime());
                    company.setUpdateTime(updateDate);
                }

                // 简介
                String synopsis = rs.getString("synopsis");

                User user = new User();
                // 如果单位登录账号为admin，增加插入顺序
                if ("admin".equals(loginName)) {
                    j ++;
                    loginName = loginName + j;
                }
                user.setUsername(loginName);
                user.setUserType("3");
                user.setPassword(userService.encodePsw(IdUtil.simpleUUID()));
                user.setName(name);

                company.setName(name);
                company.setDeleted("0".equals(del_flag) ? 0 : 1);
                company.setOldId(oldId);
                company.setOrganizationCode(organization);
                company.setBusinessUrl(minioUrl + "/zuel/old" + organizationUrl.substring(organizationUrl.lastIndexOf("/")));
                company.setLegalPersons(minioUrl + "/zuel/old" + businessurl.substring(businessurl.lastIndexOf("/")));

                company.setNature(getNewValue(oldNatureList, natureList, nature));
                company.setIndustry(getNewValue(oldIndustryList, industryList, industry));
                company.setScale(String.valueOf(scale));

                String city = getName(areaid, areaList);
                company.setCity(city);
                String province = getProvince(areaid, areaList);
                company.setProvince(province);
                company.setCompanyLevel(String.valueOf(locationlevel));
                company.setContactName(contact);
                company.setContactMobile(tel);
                company.setAddress(address);
                company.setZipCode(zipcode);
                company.setState(getState(checkstate));
                company.setStateContent(checkstatecontent);
                company.setContent(synopsis);
                company.setContent(synopsis != null ? synopsis.replaceAll("/ueditor/jsp/upload", contentUrl + "/ueditor/jsp/upload") : "");
                company.setWebsite(homepage);
                company.setCreateTime(createDate);
                i ++;
                log.info("单位：{} 数量：{} 个", name, i);
                if (userService.save(user)) {
                    company.setUserId(user.getUserId());
                    companyService.save(company);
                    UserRole userRole = new UserRole();
                    if (CompanyConstants.COMPANY_STATE_PASS.equals(company.getState())) {
                        // 审核通过
                        userRole.setRoleId(4);
                    } else {
                        // 其他状态
                        userRole.setRoleId(7);
                    }
                    userRole.setUserId(user.getUserId());
                    userRoleService.save(userRole);
                    log.info("插入单位：{} 数量：{} 个", name, i);
                }
            }
            long overTime = System.currentTimeMillis();
            long finishTime = (overTime - beginTime) / 1000;
            log.info("完成时间 : {} 分", finishTime);
            return JsonResult.ok(String.format("完成时间 : %s 分", finishTime));
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
                st.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return JsonResult.ok();
    }

    /** 
     * 查询所有地区
     */
    private List<Map<String, Object>> getAreaList() {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, userName, password);
            st = (Statement) conn.createStatement();
            String sql = "select id,parent_id,name from sys_area";
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                String id = rs.getString("id");
                String parentId = rs.getString("parent_id");
                String name = rs.getString("name");
                map.put("id", id);
                map.put("parentId", parentId);
                map.put("name", name);
                list.add(map);
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
                st.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    /** 
     * 获取id查询所在城市
     * @param id
     * @return 
     */
    private String getName(String id, List<Map<String, Object>> list) {
        for (Map<String, Object> map : list) {
            String areaId = (String) map.get("id");
            if (id.equals(areaId)) {
                String name = (String) map.get("name");
                return name.trim().replaceAll(" ", "");
            }
        }
        return "";
    }

    /**
     * 获取id查询所在省份
     * @param id
     * @return
     */
    private String getProvince(String id, List<Map<String, Object>> list) {
        for (Map<String, Object> map : list) {
            String areaId = (String) map.get("id");
            if (id.equals(areaId)) {
                String parentId = (String) map.get("parentId");
                String name = getName(parentId, list);
                return name.trim().replaceAll(" ", "");
            }
        }
        return "";
    }

    private String getState(Integer state) {
        switch (state) {
            case 0 : return "0";
            case 1 : return "1";
            case -1 : return "2";
            case -2 : return "3";
            default: return "";
        }
    }

    /** 
     * 转换
     * @param oldList
     * @param newList
     * @param value
     * @return 
     */
    private String getNewValue(List<Dict> oldList, List<Dict> newList, Integer value) {
        String dictValue = String.valueOf(value);
        for (Dict dict : oldList) {
            if (dictValue.equals(dict.getValue())) {
                for (Dict dictionary : newList) {
                    if (dict.getLabel().equals(dictionary.getLabel())) {
                        return dictionary.getValue();
                    }
                }
            }
        }
        return null;
    }


}
